package dao.impl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import javax.naming.InitialContext;
import javax.sql.DataSource;

import pojo.Customer;
import dao.CustomerDao;

public class CustomerDaoImpl implements CustomerDao {

	@Override
	public int addCustomer(Customer c) {
		Connection conn = getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlA = "insert into customer(id, cname, sal , sex, birth) values(cid_seq.nextval,?,?,?,?)";
			String generatedColumns[] = { "ID" };
			PreparedStatement ps = conn
					.prepareStatement(sqlA, generatedColumns);

			ps.setString(1, c.getCname());
			ps.setDouble(2, c.getSal());
			ps.setBoolean(3, c.isSex());
			ps.setDate(4, new java.sql.Date(c.getBirth().getTime()));
			int m = ps.executeUpdate();
			// ps.execute();
			int cId = 0;
			ResultSet setA = ps.getGeneratedKeys();
			if (setA.next()) {
				cId = setA.getInt(1);
				System.out.println("剛插入的用戶的ID： " + cId);
			}

			String[] favs = c.getFavs();
//			if(favs!=null)
//			System.out.println("即将要插入：customer_fav 的 favs: "+ Arrays.toString(favs));
			
			if (favs != null && favs.length > 0) {
				String sqlB = "insert into customer_fav(id, cid, fid) values(cfid_seq.nextval, ?, ?)";
				ps = conn.prepareStatement(sqlB);
				for (String fav : favs) {
					ps.setInt(1, cId);
					ps.setInt(2, Integer.parseInt(fav));
					ps.executeQuery();
				}
			}
			conn.commit();
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return 0;
		}
	}

	@Override
	public List<Customer> findAllCustomers() {
		List<Customer> list = new ArrayList<Customer>();
		String sql = "select * from customer";
		String sqlB = "select e2.fname from customer_fav e1 left join fav e2 on e1.fid=e2.id where e1.cid=?";
		Connection conn = getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet set = ps.executeQuery();
			while (set.next()) {
				Customer c = new Customer();
				c.setCname(set.getString("cname"));
				c.setId(set.getInt("id"));
				java.sql.Date dd = set.getDate("birth");
				if (dd != null) {
					c.setBirth(new Date(dd.getTime()));
				}
				c.setSal(set.getDouble("sal"));
				c.setSex(set.getBoolean("sex"));

				// TODO: 获取每个用户的爱好列表
				List<String> favList = new ArrayList<String>();
				ps = conn.prepareStatement(sqlB);
				ps.setInt(1, c.getId());
				ResultSet favSet = ps.executeQuery();
				while (favSet.next()) {
					favList.add(favSet.getString("fname"));
				}
				String[] favStrs = new String[favList.size()];
				for (int i = 0; i < favList.size(); i++) {
					favStrs[i] = favList.get(i);
				}
				if (favStrs != null && favStrs.length > 0)
					c.setFavs(favStrs);
				list.add(c);
			}

			return list;
		} catch (SQLException e) {
			return null;
		}
	}

	@Override
	public int updateCustomer(Customer c) {
		Connection conn = getConnection();
		try {
			conn.setAutoCommit(false);
			String sqlA = "update customer set cname=?,sal =?,sex=?,birth=? where id=?";
			PreparedStatement ps = conn
					.prepareStatement(sqlA);

			ps.setString(1, c.getCname());
			ps.setDouble(2, c.getSal());
			ps.setBoolean(3, c.isSex());
			ps.setDate(4, new java.sql.Date(c.getBirth().getTime()));
			ps.setInt(5,c.getId());
			int m = ps.executeUpdate();
			// ps.execute();
			int cId = c.getId();
			

			//先delete
			String sqlDel = "delete customer_fav where cid=?";
			ps = conn.prepareStatement(sqlDel);
			ps.setInt(1, c.getId());
			ps.executeUpdate();
			
			
			//再插入
			String[] favs = c.getFavs();
			if (favs != null && favs.length > 0) {
				String sqlB = "insert into customer_fav(id, cid, fid) values(cfid_seq.nextval, ?, ?)";
				ps = conn.prepareStatement(sqlB);
				for (String fav : favs) {
					ps.setInt(1, cId);
					ps.setInt(2, Integer.parseInt(fav));
					ps.executeQuery();
				}
			}
			conn.commit();
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return 0;
		}
	}

	@Override
	public int deleteCustomer(Integer id) {
		Connection conn = getConnection();
		String sql = "delete customer where id=?";
		String sqlB = "delete customer_fav where cid=?";
		try{
			PreparedStatement ps = conn.prepareStatement(sqlB);
			ps.setInt(1, id);
			ps.executeUpdate();
			
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
			conn.commit();
			return 1;
		}catch(SQLException e){
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return 0;
		}
	}

	@Override
	public Customer searchCustomer(Integer cid) {
		Connection conn = getConnection();
		String sql = "select * from customer where id=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, cid);
			ResultSet set = ps.executeQuery();
			if (set.next()) {
				Customer c = new Customer();
				c.setCname(set.getString("cname"));
				c.setId(set.getInt("id"));
				java.sql.Date dd = set.getDate("birth");
				if (dd != null) {
					c.setBirth(new Date(dd.getTime()));
				}
				c.setSal(set.getDouble("sal"));
				c.setSex(set.getBoolean("sex"));

				///获取fav
				
				return c;
			}
		} catch (SQLException e) {
			return null;
		}
		return null;
	}

	private Connection getConnection() {
		try {
			InitialContext cxt = new InitialContext();// / JNDI 树
			DataSource ds = (DataSource) cxt
					.lookup("java:/comp/env/jdbc/myoracle");// // 查找 [tomcat
															// 协议]/jdbc/xxxx
			Connection conn = ds.getConnection();
			// System.out.println(conn);
			return conn;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public Customer searchCustomer(String cname) {
		Connection conn = getConnection();
		String sql = "select * from customer where cname=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, cname);
			ResultSet set = ps.executeQuery();
			if (set.next()) {
				Customer c = new Customer();
				c.setCname(set.getString("cname"));
				c.setId(set.getInt("id"));
				java.sql.Date dd = set.getDate("birth");
				if (dd != null) {
					c.setBirth(new Date(dd.getTime()));
				}
				c.setSal(set.getDouble("sal"));
				c.setSex(set.getBoolean("sex"));

				return c;
			}
		} catch (SQLException e) {
			return null;
		}
		return null;
	}

}
